
package cc.bookcloud.tool.ocr.controller;


import cc.bookcloud.tool.ocr.common.excel.*;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import cc.bookcloud.tool.ocr.common.JResult;
import cc.bookcloud.tool.ocr.common.dbscript.ExcelTableFieldRequVo;
import cc.bookcloud.tool.ocr.util.ExcelUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationEventPublisher;
import org.springframework.util.ResourceUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.List;
import java.util.Map;

@Slf4j
@RestController
@Api(tags = "一体化编译发版辅助小工具", value = "一体化编译发版辅助小工具")
@RequestMapping("/release")
public class DbScriptController {


    // 默认bucket
    private static final String _BUCKET_DEFAULT = "jzsoft";

    // 访问路径
    private static final String _ACCESS_PATH = "/oss/download?file=";


    private static final String ln = "\r\n";
    private static final String tn = "\t";

    private static final String link = "LOGGING\n" +
            "NOCOMPRESS\n" +
            "PCTFREE 10\n" +
            "INITRANS 1\n" +
            "STORAGE (\n" +
            "  INITIAL 65536\n" +
            "  NEXT 1048576\n" +
            "  MINEXTENTS 1\n" +
            "  MAXEXTENTS 2147483645\n" +
            "  BUFFER_POOL DEFAULT\n" +
            ")\n" +
            "PARALLEL 1\n" +
            "NOCACHE\n" +
            "DISABLE ROW MOVEMENT\n" +
            ";\n";


    @Autowired
    private ApplicationEventPublisher eventPublisher;

    @Autowired
    private ApplicationContext context;

    /**
     * desc  Excel解析转换成SQL语句
     *
     * @param file:
     * @return JResult<String>
     * @author oycq
     * @date 2022/5/24 9:35
     */
    @PostMapping("/dbscript/convert")
    public JResult<String> convert(@RequestParam("files") MultipartFile file) throws IOException {
        JResult<String> result = JResult.build();
        try {
            if (file == null) {
                result.setMsg("上传文件为空");
                return result;
            }
            //读取并收集Excel文件对应的数据
            ExcelReader build = EasyExcel.read(file.getInputStream()).build();
            List<ReadSheet> readSheets = build.excelExecutor()
                    .sheetList();
            for (ReadSheet readSheet : readSheets) {
                System.out.println(readSheet.getSheetName() + "," + readSheet.getSheetNo());
                //第二部分从第3行开始读
                readSheet = EasyExcel.readSheet(readSheet.getSheetNo()).head(ColumnProperty.class).registerReadListener(new EasyExcelListener()).headRowNumber(2).build();
                build.read(readSheet);
            }
            //转换得到脚本
            List<StringBuffer> oracleSqlList = TableConvertDbScriptEngine.convert("ORACLE");
            List<StringBuffer> dmSqlList = TableConvertDbScriptEngine.convert("DM");
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("-- ********** oracle  {}-->start ********** ").append(ln);
            for (StringBuffer buffer : oracleSqlList) {
                // sqlBuffer.append("-- ********** oracle  {}-->start ********** ").append(ln);
                sqlBuffer.append(ln);
                sqlBuffer.append(buffer.toString()).append(ln);
            }
            sqlBuffer.append("-- ********** oracle  {}-->end********** \n");
            sqlBuffer.append("\n");
            sqlBuffer.append("\n");
            sqlBuffer.append("\n");


            sqlBuffer.append("-- ********** DM  {}-->start********** ").append(ln);
            for (StringBuffer buffer : dmSqlList) {
                sqlBuffer.append(buffer.toString()).append(ln);
            }
            sqlBuffer.append("-- ********** DM  {}-->end********** ").append(ln);
            log.info("====================== sql log =====================");
            log.info(sqlBuffer.toString());
            //将脚本内容写入文件,前端根据文件名获取文件
            String fileName = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli() + ".sql";
            String path = ResourceUtils.getURL("classpath:").getPath();
            String filePath = path + "static/DBFile/" + fileName;
            SQLUtils.writeToSql(sqlBuffer.toString(), filePath);
            result.setData(fileName);
            //删除存储文件目录文件夹下无用的文件
            eventPublisher.publishEvent(new DeleteFileEvent(context));
            //清理当前线程 清空结果在FSInterceptor类处理
        } catch (IOException e) {
            result.setMsg("解析失败");
            result.setData(e.getMessage());
            e.printStackTrace();
        }
        return result;
    }


    /**
     * @param files
     * @param request
     * @return JResult<String>
     * @desc：数据为脚本生成
     * @author：Test
     * @date：2022/5/16 11:46
     */
    @ApiOperation(value = "文件上传")
    @PostMapping("/dbscript/upload")
    public JResult<Map<String, Object>> uploadFiles(@RequestParam("files") MultipartFile[] files, HttpServletRequest request) {
        log.info("[OSS文件上传] 请求参数==> 文件数量：" + files.length);
        JResult<Map<String, Object>> result = JResult.build();
        Map<String, Object> map = null;
        try {
            for (MultipartFile file : files) {
                map = readExcel(file);
                String fileType = file.getContentType();
                String name = file.getOriginalFilename();
                long size = file.getSize();
                log.info("-- 文件名称：" + name + ",大小：" + size + ",类型：" + fileType);
            }
        } catch (Exception e) {
            result.failed("上传文件失败:" + e.getMessage());
            log.error("*** [OSS文件上传] 上传文件发生了异常", e);
        }
        return result.ok(map);
    }


    /**
     * desc 将前端表格信息转换成SQL语句
     *
     * @param dto:
     * @return JResult<String>
     * @author oycq
     * @date 2022/5/17 16:22
     */
    @ApiOperation(value = "转换脚本")
    @PostMapping("/dbscript/conversion")
    public JResult<String> conversion(@RequestBody ExcelTableFieldDTO dto) {
        JResult<String> result = JResult.build();
        StringBuilder builder = new StringBuilder();
        StringBuilder builder2 = new StringBuilder();
        stitchingSQL(dto.getTableName(), builder, builder2, dto.getList(), dto.getDbType());
        String sql = builder.toString();
        sql = "oracle".equals(dto.getDbType()) ? sql + ln + link + builder2.toString() : sql + ln + builder2.toString();
        result.setData(sql);
        return result;
    }


    @Data
    static class ExcelTableFieldDTO {
        private String tableName;
        private String tableDesc;
        private List<ExcelTableFieldRequVo> list;
        private String dbType;
    }


    private void stitchingSQL(String tableName, StringBuilder builder, StringBuilder builder2, List<ExcelTableFieldRequVo> data, String dbType) {
        builder.append("CREATE TABLE ").append(tableName).append("(").append(ln);
        int count = 0;
        for (ExcelTableFieldRequVo fieldInfo : data) {
            builder.append(tn).append(fieldInfo.getColumn()).append(" ").append(fieldInfo.getType());
            if (!StringUtils.isEmpty(fieldInfo.getLen())) {
                builder.append("(").append(fieldInfo.getLen()).append(")");
            }
            if ("否".equals(fieldInfo.getNullVal())) {
                builder.append(" NOT NULL ");
            }
            if ("是".equals(fieldInfo.getPrimary())) {
                builder.append("PRIMARY KEY ");
            }
            if (count != data.size() - 1) {
                builder.append(",");
            }
            builder.append(ln);
            count++;


            if (fieldInfo.getDesc().contains("&nbsp;")) {
                String replace = fieldInfo.getDesc().replace("&nbsp;", "");
                fieldInfo.setDesc(replace);
            }
            builder2.append("COMMENT ON COLUMN").append(" ").append(tableName).append(".").append(fieldInfo.getColumn())
                    .append(" IS ").append("'").append(fieldInfo.getDesc()).append("'").append(";").append(ln);
        }
        if ("oracle".equals(dbType)) {
            builder.append(")");
        } else {
            builder.append(");");
        }
    }

    public Map<String, Object> readExcel(MultipartFile file) {
        // for ,parse excel
        return ExcelUtil.conversion(file);
    }


}
